Connecting to SharePoint and Microsoft Online Services in Power BI

Course: Microsoft Power BI Training

Topic: Connecting to SharePoint and Microsoft Online Services

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to SharePoint and Microsoft Online Services

Microsoft Power BI connects to many data sources, including databases, spreadsheets, cloud platforms, business applications, and online services. SharePoint Online and other Microsoft cloud services are particularly useful because organizations often store operational information, documents, and collaborative business data in Microsoft 365.

SharePoint Online is a cloud-based collaboration platform that organizations use to store documents, create lists, manage team information, and share resources. Power BI can connect to SharePoint lists and files stored in SharePoint document libraries to transform that information into interactive reports and dashboards.

Microsoft Online Services is a broad term for Microsoft's cloud-based applications and services. Depending on the connector and available permissions, Power BI can work with sources such as SharePoint Online, OneDrive for work or school, Microsoft Dataverse, and other supported cloud data sources.

For example, a company might store employee information in a SharePoint list, monthly sales files in a document library, and project progress in another list. Power BI can retrieve these sources, clean the data, and present the results in a single report.

Learning Objectives

• Understand SharePoint Online and Microsoft cloud data sources.
• Learn how to connect Power BI Desktop to a SharePoint Online list.
• Understand how to connect to files stored in SharePoint document libraries.
• Differentiate SharePoint Online List and SharePoint Folder connectors.
• Connect to supported Microsoft Online Services using appropriate connectors.
• Authenticate securely using the correct organizational account.
• Transform SharePoint data using Power Query Editor.
• Combine multiple files and lists for reporting.
• Configure refresh and troubleshoot connection issues.
• Apply security, data quality, and performance best practices.

2. Understanding SharePoint Online

SharePoint Online allows teams to organize information in sites, document libraries, and lists. These structures can provide useful data sources for Power BI reports.

2.1 SharePoint Sites

A SharePoint site is a workspace for organizing information related to a team, department, project, or business function. A site may contain document libraries, lists, pages, and other resources.

Example site URL:

https://contoso.sharepoint.com/sites/SalesTeam

This is an illustrative URL. Replace it with the actual SharePoint site address available to your organization and account.

2.2 SharePoint Document Libraries

A document library stores files such as Excel workbooks, CSV files, Word documents, PDFs, and other business documents. Power BI can connect to supported files in a library and use them as data sources.

For example, a Sales Reports document library might contain:

• JanuarySales.xlsx
• FebruarySales.xlsx
• MarchSales.xlsx
• RegionalTargets.xlsx

These files can be loaded individually or combined when they share a compatible structure.

2.3 SharePoint Lists

A SharePoint list stores structured information in columns and rows, similar to a table. Lists are often used to track requests, tasks, customers, projects, assets, and approvals.

Power BI can analyze this list to calculate total costs, count requests by status, and compare spending by department.

3. Understanding Microsoft Online Services

Microsoft Online Services include cloud applications and platforms that help organizations manage documents, collaboration, business processes, and structured information.

Not every service has a direct connector that exposes all its data to Power BI. Choose the appropriate supported connector and confirm that your account has the required access.

4. Understanding Power BI Connectors

A connector provides the interface through which Power BI accesses a particular data source. Different SharePoint scenarios require different connectors.

4.1 Choosing the Correct Connector

• Use SharePoint Online List when the data is stored in a SharePoint list.
• Use SharePoint Folder when you need to find or combine files stored in a SharePoint document library.
• Use the appropriate file connector when connecting to an individual Excel or CSV file.
• Use an appropriate supported cloud connector for services such as Dataverse.
• Use the Web or OData connector only when the source is compatible with that connector and its authentication requirements.

5. Prerequisites for Connecting to SharePoint

Before connecting Power BI to SharePoint or another Microsoft cloud service, make sure you have the following requirements in place.

5.1 Finding the SharePoint Site URL

1. Open the required SharePoint site in your browser.
2. Navigate to the site that contains the list or document library.
3. Copy the site address from the browser or the site's navigation.
4. Confirm that the address points to the correct site and not just an individual document or a long sharing link.
5. Use the site URL required by the selected connector.

Example of a site URL:

https://contoso.sharepoint.com/sites/Marketing

Use the actual site URL for your organization. For SharePoint list and folder connectors, Power BI generally expects the site address rather than a link to an individual document or a deep folder path.

6. Connecting to a SharePoint Online List

The SharePoint Online List connector is designed to retrieve structured data from SharePoint Online lists. It is useful for reporting on project tasks, service requests, employee records, issue trackers, and other list-based business information.

6.1 Step-by-Step Process

1. Open Power BI Desktop.
2. Select Home > Get data > More.
3. Choose Online Services and select SharePoint Online List.
4. Select Connect.
5. Enter the SharePoint site URL.
6. Select the appropriate connector implementation if the dialog provides a choice.
7. Select OK.
8. Choose the supported authentication method and sign in with the account that has access to the site.
9. In Navigator, select the list you want to analyze.
10. Select Transform Data if you need to clean the data before loading it.
11. Alternatively, select Load to import the selected list directly.
12. Review the table in Power BI and verify that the required columns have loaded correctly.

6.2 Example: Connecting to an Employee Requests List

Suppose a SharePoint list named EmployeeRequests contains the following fields:

After connecting, Power BI can create reports that show:

• Total requests submitted.
• Requests grouped by department.
• Approved, pending, and rejected request counts.
• Requests submitted over time.
• Department-level workload and trends.

7. Connecting to Files in a SharePoint Document Library

Many organizations store Excel workbooks, CSV files, and other reporting files in SharePoint document libraries. The SharePoint Folder connector helps Power BI discover files at a SharePoint site and select files for analysis.

7.1 Step-by-Step Process

1. Open Power BI Desktop.
2. Select Home > Get data > More.
3. Search for and select SharePoint Folder.
4. Select Connect.
5. Enter the SharePoint site URL.
6. Authenticate using the account authorized to access the site.
7. Review the file inventory returned by the connector.
8. Filter the list by folder path, file name, or extension to locate the required files.
9. Choose Transform Data to inspect and filter the file inventory.
10. For compatible files with matching structures, use the available combine option to combine their contents.
11. Inspect the resulting data and correct data types before loading it.

7.2 Example: Monthly Sales Files

Imagine that the SalesReports library contains monthly files with the same column structure.

SalesReports/
    JanuarySales.xlsx
    FebruarySales.xlsx
    MarchSales.xlsx
    AprilSales.xlsx

Each file contains the columns OrderID, OrderDate, Region, Product, Quantity, and SalesAmount.

Power BI can use the SharePoint Folder connector to locate the files and combine their records into a single table. This helps create a sales report covering several months without manually copying every worksheet into one workbook.

7.3 Important File-Combining Considerations

• Make sure the files have compatible column names and data structures.
• Filter out temporary files, archived files, and unrelated documents.
• Exclude hidden or system files when they are not part of the reporting data.
• Check for duplicate records before aggregating results.
• Confirm that dates, numbers, and currencies are interpreted correctly.
• Check whether a new file added to the folder will be included by the query after refresh.

8. Connecting to an Individual Excel or CSV File Stored in SharePoint

If the reporting data is stored in a single file, you may be able to use the appropriate file connector or the Web connector with a suitable SharePoint file URL and authentication configuration. The best method depends on the file, connector, and environment.

8.1 General Workflow for an Excel Workbook

1. Confirm that the workbook is stored in SharePoint Online or OneDrive for work or school.
2. Identify the correct file and its location.
3. Choose the supported connector and provide the required file path or URL.
4. Authenticate with an account that can access the workbook.
5. Select the worksheet or named table containing the reporting data.
6. Choose Transform Data if you need to clean or standardize the data.
7. Load the required data into Power BI.

8.2 General Workflow for a CSV File

1. Locate the CSV file in the SharePoint document library.
2. Choose a connector that supports access to the file through its location and authentication method.
3. Inspect the delimiter, encoding, and header row.
4. Confirm that the columns are split correctly.
5. Set data types for date, numeric, and text columns.
6. Remove unnecessary columns and rows.
7. Load the transformed data into the Power BI model.

Important: A browser sharing link is not always a valid direct-download URL for Power BI. If a connection fails, use the appropriate SharePoint site or file connector and verify the URL format and authentication settings.

9. Authentication and Access Permissions

Authentication verifies the identity of the user connecting to a Microsoft Online Service. Authorization determines what that user is allowed to access.

9.1 Organizational Account Authentication

SharePoint Online connections commonly use a Microsoft organizational account. During the first connection, Power BI may prompt you to sign in and grant access to the site through the supported authentication flow.

1. Select the appropriate authentication option in the connection dialog.
2. Sign in using the Microsoft 365 account approved for the data source.
3. Complete any required multi-factor authentication.
4. Choose the correct credential scope if prompted.
5. Connect and verify that the required lists or files appear.

9.2 Permission Requirements

• The account must have permission to read the relevant SharePoint site and list or library.
• Access to one site does not automatically mean access to every site in the organization.
• A user may be able to open a file in a browser but still have a problem with an incorrectly configured Power BI connection.
• Published reports need suitable credentials and source access for their refresh or query requirements.
• Use only approved accounts and avoid sharing personal credentials.

9.3 Credential Management

Power BI can store data source credentials for supported connections. If the account password changes, access is revoked, or an authentication token expires, the connection may need to be reauthenticated.

• Use organizational authentication policies.
• Do not place credentials in M queries, report descriptions, or shared documents.
• Review saved data source permissions when troubleshooting authentication problems.
• Use approved service identities or managed identity options when supported by the relevant environment and connector.

10. Transforming SharePoint Data with Power Query

Power Query Editor is used to clean, reshape, and prepare data before it is loaded into the Power BI model. It is particularly helpful when SharePoint lists contain system columns, files have inconsistent structures, or records require standardization.

10.1 Common Transformations

10.2 Example: Cleaning a SharePoint List

1. Open Power Query Editor by selecting Transform Data.
2. Select the SharePoint list query.
3. Review the available columns and identify fields that are not needed.
4. Remove unnecessary columns.
5. Set RequestDate to the correct date type.
6. Set EstimatedCost to a numeric or currency-appropriate type.
7. Filter records based on the required business criteria.
8. Check for duplicate identifiers and missing values.
9. Preview the final result and select Close & Apply.

10.3 Handling Complex SharePoint Columns

SharePoint lists can include columns that represent people, lookup values, choices, attachments, hyperlinks, or other structured information. These fields may appear as nested records, lists, or tables when loaded into Power Query.

• Expand structured fields when you need specific attributes.
• Keep only the nested fields required for the report.
• Check whether a lookup field contains an identifier, a display value, or both.
• Review person fields carefully and avoid exposing unnecessary personal information.
• Set explicit data types, particularly for Boolean, date/time, and numeric fields.

11. Combining Data from Multiple Microsoft Online Sources

Power BI can combine data from multiple supported sources to provide a more complete view of business activity. For example, a SharePoint project list can be combined with an Excel budget workbook stored in OneDrive or a department reference table from another approved source.

11.1 Example Data Sources

11.2 Steps to Combine the Data

1. Connect to the SharePoint Projects list.
2. Connect to the budget workbook using an appropriate connector.
3. Connect to the Team list if it is needed for the report.
4. Standardize key columns such as ProjectID and TeamID.
5. Remove duplicates and correct data types.
6. Use Merge Queries in Power Query when combining columns from matching records.
7. Use Append Queries when stacking compatible rows from different tables.
8. Load the data and create appropriate model relationships.
9. Validate project counts and budget totals against the original sources.

11.3 Merge vs. Append

12. Using SharePoint Data in Power BI Reports

After loading and transforming the data, you can create visuals that make the information easier to understand.

12.1 Common Visualizations

• Card: Displays total requests, total budget, or completed projects.
• Bar chart: Compares requests or costs by department.
• Line chart: Shows trends in requests or spending over time.
• Donut chart: Displays the proportion of records by status when a simple part-to-whole comparison is appropriate.
• Table: Shows individual records and selected details.
• Matrix: Compares measures across departments, teams, or project categories.
• Slicer: Lets users filter the report by date, department, status, or category.

12.2 Example DAX Measures

Assume the loaded SharePoint list is named EmployeeRequests.

Total Requests

Total Requests =
COUNTROWS(EmployeeRequests)

Approved Requests

Approved Requests =
CALCULATE(
    [Total Requests],
    EmployeeRequests[Status] = "Approved"
)

Total Estimated Cost

Total Estimated Cost =
SUM(EmployeeRequests[EstimatedCost])

Pending Requests

Pending Requests =
CALCULATE(
    [Total Requests],
    EmployeeRequests[Status] = "Pending"
)

These examples assume the table and column names match your model and that EstimatedCost has a suitable numeric data type. If each row represents something other than one request, adjust the counting logic to match the data structure.

13. Refreshing SharePoint and Microsoft Online Data

Data refresh updates an imported Power BI model with the latest data retrieved from the source. For SharePoint Online, refresh behavior depends on the connector, credentials, source availability, and the report's configuration.

13.1 Refresh in Power BI Desktop

1. Open the Power BI report.
2. Confirm that the SharePoint site and files are accessible.
3. Verify that the saved credentials remain valid.
4. Select Refresh on the Home ribbon.
5. Wait for Power BI to retrieve the source data.
6. Review any errors in the refresh process.
7. Verify that record counts and important totals remain correct.

13.2 Refresh in Power BI Service

After publishing a report, configure the data source connection and refresh settings in Power BI Service as appropriate.

• Confirm that the data source is supported for the intended refresh configuration.
• Verify that the connection uses the correct organizational account or supported authentication method.
• Confirm that the account has access to every required SharePoint site, list, or file.
• Configure the refresh schedule according to business requirements and applicable service limits.
• Monitor refresh history to identify failed refreshes.
• Reauthenticate when credentials or access permissions change.
• Check gateway requirements if the solution also uses on-premises data sources.

SharePoint Online is a cloud service, so a gateway is not normally needed solely for a standard supported SharePoint Online connection. A gateway may still be required for other on-premises sources in the same model or for particular configurations.

14. Common Errors and Troubleshooting

14.1 Troubleshooting Checklist

1. Confirm the source type: SharePoint list, SharePoint folder, individual file, or another Microsoft service.
2. Verify the site URL and ensure it points to the correct site.
3. Check the account used for authentication.
4. Confirm read permissions to the specific source.
5. Test whether the list or file can be opened using the same account.
6. Review query steps for invalid paths or references to renamed columns.
7. Check whether source files have changed their structure.
8. Set explicit data types and inspect problematic values.
9. Review the refresh history in Power BI Service after publication.
10. Reauthenticate or ask the SharePoint administrator for help if permissions or tenant policies are blocking access.

15. Security and Governance Best Practices

SharePoint and Microsoft Online Services often contain confidential business information. Data access and report distribution must be handled carefully.

• Use authorized organizational accounts.
• Follow the principle of least privilege.
• Do not share confidential site URLs, file contents, or credentials with unauthorized people.
• Use approved organizational authentication and multi-factor authentication policies.
• Review report access before sharing or publishing dashboards.
• Apply Power BI row-level security when users need different row-level views, and test the rules thoroughly.
• Remember that report security does not replace source permissions or organizational governance.
• Remove sensitive columns that are not required for analysis.
• Use approved retention and data classification practices.
• Review access when employees change roles or leave the organization.

16. Performance Optimization Best Practices

16.1 Optimize the Source Data

• Retrieve only the lists, files, columns, and records required for reporting.
• Use consistent schemas across files intended for combination.
• Keep source columns well defined and avoid unnecessary duplicate records.
• Use appropriate filters to exclude unrelated files from SharePoint Folder queries.
• Organize reporting files into clearly defined document libraries or folders.

16.2 Optimize Power Query

• Remove unnecessary columns early in the query when practical.
• Filter unrelated rows and files before performing expensive transformations.
• Set data types explicitly.
• Avoid expanding complex nested fields that are not needed.
• Use reusable transformation steps when the same data-cleaning logic applies to multiple sources.
• Check refresh duration after major query changes.

16.3 Optimize the Power BI Model

• Build clear relationships between related tables.
• Use a star schema where suitable.
• Remove unused columns and tables.
• Create measures for important business calculations.
• Avoid unnecessarily complex visuals and excessive numbers of visual elements on one page.
• Validate report performance using representative data and expected user interactions.

17. Practical Project: SharePoint Project Tracking Dashboard

In this project, you will connect Power BI to a SharePoint project list and an Excel budget file stored in a document library. You will transform the data, build a model, and create a project tracking dashboard.

17.1 Sample SharePoint Projects List

17.2 Sample Budget File

17.3 Project Steps

1. Open Power BI Desktop and select Get data.
2. Connect to the SharePoint Online List connector.
3. Enter the site URL and authenticate.
4. Select the Projects list and load or transform the data.
5. Connect to the SharePoint Folder containing the budget workbook.
6. Filter the file inventory to locate the correct workbook.
7. Extract the required budget table and standardize the data types.
8. Check that ProjectID is consistent in both sources.
9. Merge the data in Power Query or load both tables and establish a suitable model relationship.
10. Create measures for project count, approved budget, and actual cost.
11. Build visuals for project status, department workload, and budget performance.
12. Add slicers for department and status.
13. Compare project totals and costs with the original SharePoint and Excel sources.
14. Save and publish the report if your environment permits.
15. Configure and test the appropriate refresh settings.

17.4 Example DAX Measures

Assume the project table is named Projects and the budget table is named ProjectBudget.

Total Projects

Total Projects =
DISTINCTCOUNT(Projects[ProjectID])

Total Approved Budget

Total Approved Budget =
SUM(ProjectBudget[ApprovedBudget])

Total Actual Cost

Total Actual Cost =
SUM(ProjectBudget[ActualCost])

Budget Remaining

Budget Remaining =
[Total Approved Budget] - [Total Actual Cost]

These measures assume that the column names and relationships match the model. Check the table grain and relationship behavior to prevent costs from being duplicated when project data is combined with other tables.

17.5 Expected Outcome

• A report connected to SharePoint project information.
• Budget data retrieved from a document library.
• Cleaned and correctly typed source columns.
• Measures showing project counts and financial metrics.
• Interactive visuals for project status and budget performance.
• A documented refresh and troubleshooting process.

18. Practice Exercises

1. Connect Power BI to an authorized SharePoint Online list.
2. Identify the difference between a SharePoint site URL and an individual file-sharing URL.
3. Connect to a SharePoint document library using the SharePoint Folder connector.
4. Filter the file inventory to show only Excel files from a specific folder.
5. Combine two compatible monthly sales files.
6. Transform a SharePoint list by renaming columns and correcting data types.
7. Create a chart showing requests grouped by status.
8. Write a DAX measure to count distinct project identifiers.
9. Investigate a simulated authentication error and document the resolution.
10. Publish a report in an authorized workspace and review its refresh configuration.

19. Interview Questions and Answers

Q1. What is SharePoint Online?

Answer: SharePoint Online is a cloud-based collaboration platform used to manage team sites, document libraries, lists, and shared organizational information.

Q2. How can Power BI connect to SharePoint?

Answer: Power BI can connect to SharePoint lists using the SharePoint Online List connector and discover files in document libraries using the SharePoint Folder connector. Other suitable connectors can be used for supported individual files.

Q3. What is the difference between SharePoint Online List and SharePoint Folder?

Answer: SharePoint Online List retrieves structured records from a list. SharePoint Folder discovers files stored at a SharePoint site so that their contents can be selected or combined.

Q4. What information is needed to connect to SharePoint?

Answer: You generally need the correct SharePoint site URL, a supported authentication method, and permission to read the relevant list, library, or file.

Q5. Can Power BI combine multiple Excel files stored in SharePoint?

Answer: Yes. The SharePoint Folder connector can locate compatible files and support combining their contents through Power Query.

Q6. What is Power Query used for?

Answer: Power Query is used to connect to data, filter rows, remove columns, correct data types, merge or append tables, and prepare data before loading it into the Power BI model.

Q7. Why might a SharePoint list not appear in Navigator?

Answer: Possible reasons include an incorrect site URL, insufficient permissions, the wrong signed-in account, or selecting the wrong connector.

Q8. Does SharePoint Online normally require an on-premises data gateway?

Answer: A standard supported SharePoint Online connection normally does not require an on-premises gateway solely for that cloud source. A gateway may still be necessary for on-premises sources used in the same solution or for specific configurations.

Q9. How do you resolve refresh failures?

Answer: Check the data source URL, credentials, access permissions, file paths, query steps, and refresh history. Also verify any gateway requirements for on-premises sources.

Q10. How can you improve the performance of a SharePoint-based report?

Answer: Filter unrelated files, load only required columns, standardize source structures, remove unnecessary transformations, simplify the data model, and use appropriate measures and visuals.

Q11. What is the difference between Merge and Append in Power Query?

Answer: Merge combines tables using matching columns, while Append adds rows from one table to another.

Q12. Why should data types be set explicitly?

Answer: Explicit data types help Power BI interpret numbers, dates, Boolean values, and text consistently, reducing calculation errors and unexpected filtering behavior.

20. Quick Revision Checklist

• Understand SharePoint sites, document libraries, and lists.
• Know the purpose of Microsoft Online Services.
• Choose the correct connector for lists, folders, and files.
• Know how to find and enter the correct SharePoint site URL.
• Authenticate with an authorized organizational account.
• Understand list loading and file-combining workflows.
• Use Power Query to clean data and handle structured columns.
• Know when to merge and append queries.
• Build measures and visuals from SharePoint data.
• Understand refresh configuration and credential management.
• Know common authentication, URL, and refresh errors.
• Apply security, governance, and performance best practices.

21. Official Learning Resources

• Microsoft Learn: Create a Report on a SharePoint List in Power BI Desktop
• Microsoft Learn: SharePoint Online List Connector
• Microsoft Learn: SharePoint Folder Connector
• Microsoft Learn: Import Files from SharePoint and OneDrive for Work or School

22. Conclusion

Connecting to SharePoint and Microsoft Online Services allows Power BI users to analyze information stored across collaborative cloud platforms. SharePoint lists provide structured records, while document libraries provide access to files that can be imported and combined for reporting.

By choosing the correct connector, using the right site URL, authenticating securely, transforming data with Power Query, and configuring refresh appropriately, you can build reliable dashboards from Microsoft cloud data. Always validate the data, protect confidential information, and test refresh behavior before distributing reports to business users.

Explore the course: Microsoft Power BI Training at JustAcademy

Register for a course demo: Register for a JustAcademy Course Demo

Visit the website: JustAcademy